from datetime import datetime,timedelta

from fastapi import APIRouter, HTTPException

router = APIRouter()

from typing import List
from .models.found_everyday_siprice import  FoundEverydaySiPrice
from .models.found_lot_everyday_balance import FoundLotEverydayBalance
from .models.found_lot_trade_flow import FoundLotTradeFlow
from app.tactics.models.found_lot import FoundLot



from .models.schemas import FoundEverydaySipriceCreate,FoundLotEverydayBalanceCreate,\
    FoundLotEverydayBalanceRead,FoundLotEverydayBalanceRead2,FoundLotEverydayBalanceRead3,FoundLotTradeFlowRead,FoundLotTradeFlowReadCustom

import logging
logger = logging.getLogger(__name__)
from ..database import execute_raw_sql_dict
from .rpcs.api_fund_eastmoney import get_eastmoney





@router.get("/NO.1.getSiPriceAndSave/")
async def getSiPrice():
    try:
        res = get_eastmoney()
        for each in res :
            flag =await FoundEverydaySiPrice.filter(base_date=each['base_date'])
            if not flag:
                await  FoundEverydaySiPrice.create(**each)
            else:
                #logger.info('==已存在====,%s', each['base_date'])
                pass

        return {'msg':'ok'}
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))

#更新平均价格
@router.get("/No.2.updateAvePrice/")
async def updateAvePrice():
    fileds = [('ave_5_si_price',5),('ave_10_si_price',10),('ave_20_si_price',20),('ave_30_si_price',30),
              ('ave_60_si_price',60),('ave_120_si_price',120),

              ]
    for cur_field in fileds:
        sql = """select id, %(cur_field)s,base_date from found_everyday_siprice where  coalesce(%(cur_field)s,0)=0 """%({'cur_field':cur_field[0]})
        res = await execute_raw_sql_dict(sql)
        for each in res :
            days =cur_field[1]
            rlist = await FoundEverydaySiPrice.filter(base_date__lte=each['base_date']).order_by('-base_date').limit(days)
            ave_price = round(sum([i.base_si_price for i in rlist])/len(rlist),4)
            found_everyday_price = await FoundEverydaySiPrice.get(id=each['id'])
            setattr(found_everyday_price, cur_field[0], ave_price)
            await found_everyday_price.save()

    maxmin_fields = [('max_120_si_price', 120, 'max'),
                     ('max_60_si_price',60,'max'),
                     ('max_30_si_price', 30, 'max'),
                     ('max_20_si_price', 20, 'max'),
                     ('max_10_si_price', 10, 'max'),
                     ('max_5_si_price', 5, 'max'),
                     ('min_120_si_price', 120, 'min'),
                     ('min_60_si_price', 60, 'min'),
                     ('min_30_si_price', 30, 'min'),
                     ('min_20_si_price', 20, 'min'),
                     ('min_10_si_price', 10, 'min'),
                     ('min_5_si_price', 5, 'min'),]
    for cur_field in maxmin_fields:
        sql = """select id, %(cur_field)s,base_date from found_everyday_siprice where  coalesce(%(cur_field)s,0)=0 """ % (
        {'cur_field': cur_field[0]})
        res = await execute_raw_sql_dict(sql)
        for each in res:
            days = cur_field[1]
            type =cur_field[2]
            rlist = await FoundEverydaySiPrice.filter(base_date__lte=each['base_date']).order_by('-base_date').limit(
                days)
            if type=='max':
                type_price =max([i.base_si_price for i in rlist])
            if type=='min':
                type_price = min([i.base_si_price for i in rlist])
            found_everyday_price = await FoundEverydaySiPrice.get(id=each['id'])
            setattr(found_everyday_price, cur_field[0], type_price)
            await found_everyday_price.save()


    return {'msg':'ok'}



# 获取所有余额列表
@router.post("/No.3.balance/", response_model=List[FoundLotEverydayBalanceRead3])
async def update_balance(lot_code:str = '013128-001'):
    #更新买入流水的单价和份额
    sql="""update  found_lot_trade_flow  a 
            inner join found_everyday_siprice b on b.base_date=a.trade_date and b.base_si_price<>0
            and b.found_code=a.found_code
            set a.trade_si_price=b.base_si_price,
            a.trade_shares=round(a.trade_amount/b.base_si_price,2 )
            where a.trade_type='in' and a.lot_code='%s';"""%(lot_code)
    await execute_raw_sql_dict(sql)

    #更新卖出流水的单价和金额
    sql="""update  found_lot_trade_flow  a 
            inner join found_everyday_siprice b on b.base_date=a.trade_date and b.base_si_price<>0
            and b.found_code=a.found_code
            set a.trade_si_price=b.base_si_price,
            a.trade_amount=round(a.trade_shares*b.base_si_price,2 )
            where a.trade_type='out' and a.lot_code='%s';"""%(lot_code)
    await execute_raw_sql_dict(sql)


    #核销单的卖出价格
    sql =""" update  found_lot_trade_match  a
        inner join found_everyday_siprice b on b.base_date=a.sell_date
        and a.found_code=b.found_code
        set a.sell_price=b.base_si_price
        where a.lot_code='%s';"""%(lot_code)
    await execute_raw_sql_dict(sql)

    # 核销单的利润和利润率
    sql =""" update found_lot_trade_match 
            set sell_amount=round(sell_price*match_shares,2),
            earn_amount= round(sell_price*match_shares,2)- buy_amount,
            earn_rate= round((round(sell_price*match_shares,2)- buy_amount)/buy_amount,4)
            where lot_code='%s' and sell_price>0 and sell_amount=0;"""%(lot_code)
    await execute_raw_sql_dict(sql)

    flows = await FoundLotTradeFlow.filter(lot_code=lot_code).order_by('trade_date')
    flows_dict={}
    for flow in flows:
        if not flows_dict.get(flow.trade_date):
            flows_dict[flow.trade_date] =[flow]
        else:
            flows_dict[flow.trade_date].append(flow)
    if not flows:
        return True
    found_code,found_name = flows[0].found_code,flows[0].found_name
    start_date=flows[0].trade_date
    end_date = datetime.today().date()
    cur_date=start_date
    #删除数据
    await FoundLotEverydayBalance.filter(lot_code=lot_code).delete()
    cur_balance =None
    while(cur_date<=end_date):
        cur_date_flows = flows_dict.get(cur_date, [])
        if cur_date_flows == None:
            print(1)
        cur_date_trade_shares = sum(x.trade_shares for x in cur_date_flows)
        cur_date_trade_amount = sum(x.trade_amount for x in cur_date_flows)
        if not cur_balance:
            cur_date_trade_si_price = cur_date_trade_shares and round(cur_date_trade_amount / cur_date_trade_shares,4) or 0
            cur_balance_dict={
                'lot_code':lot_code,
                'found_code':found_code,
                'found_name':found_name,
                'holding_shares':cur_date_trade_shares,
                'holding_amount':cur_date_trade_amount,
                'holding_si_price':cur_date_trade_si_price,
                'base_date':cur_date
            }
        else:
            if cur_date_trade_shares + cur_balance.holding_shares:
                cur_date_trade_si_price = round((cur_date_trade_amount + cur_balance.holding_amount) / \
                                          (cur_date_trade_shares + cur_balance.holding_shares),4)
            else:
                cur_date_trade_si_price = 0
            cur_balance_dict={
                'lot_code':lot_code,
                'found_code':found_code,
                'found_name':found_name,
                'holding_shares':cur_date_trade_shares + cur_balance.holding_shares,
                'holding_amount':cur_date_trade_amount + cur_balance.holding_amount,
                'holding_si_price':cur_date_trade_si_price,
                'base_date': cur_date
            }
        cur_balance= await  FoundLotEverydayBalance.create(**cur_balance_dict)
        cur_date=cur_date + timedelta(days=1)
    sql="""update found_everyday_siprice   a
            inner join found_lot_everyday_balance b on b.found_code=a.found_code 
            and b.base_date=a.base_date
            set b.base_si_price=a.base_si_price
            where coalesce(b.base_si_price,0)=0"""
    await execute_raw_sql_dict(sql)

    sql="""update found_lot_everyday_balance set base_amount = round(base_si_price * holding_shares,2) ;"""
    await execute_raw_sql_dict(sql)
    sql="""update found_lot_everyday_balance set total_earn_amount = base_amount - holding_amount;"""
    await execute_raw_sql_dict(sql)
    sql=""" update found_lot_everyday_balance set rate =  round(100*(base_si_price-holding_si_price)/base_si_price,2)
 where base_si_price<>0"""
    await execute_raw_sql_dict(sql)
    res = await FoundLotEverydayBalance.filter()

    return res

# 获取交易列表
@router.get("/trade/", response_model=List[FoundLotTradeFlowReadCustom])
async def read_trade():
    # 获取 ORM 实例列表
    res = await FoundLotTradeFlow.all()
    return res

# 获取交易列表
@router.post("/trade/")
async def create_trade(trade:FoundLotTradeFlowRead):
    try:
        trade_obj = await FoundLotTradeFlow.create(**trade.dict())
        return trade_obj
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))

@router.post("/ALL/")
async def updateEveryDate():
    await getSiPrice()
    await updateAvePrice()
    found_lots = await FoundLot.filter(record_status=1).all()
    for lot in found_lots:
        await update_balance(lot_code = lot.lot_code)
    return {'msg':'success'}



##################demo############
from .models.schemas import POOrderRequest,POAsnDetail,POAsnResponse
@router.post("/get-asn-details", response_model=POAsnResponse)
async def get_asn_details(request: POOrderRequest):
    try:
        po_order_list = request.poOrderList
        asn_list = []

        # 模拟数据获取过程
        if not po_order_list:
            raise HTTPException(status_code=400, detail="poOrderList cannot be empty")

        for po_order in po_order_list:
            # 假设这里的逻辑可能会抛出异常
            if po_order == "INVALID_PO":
                raise HTTPException(status_code=404, detail=f"PO order '{po_order}' not found")

            asn_list.append(POAsnDetail(poOrderCode=po_order, asnOrderNum="ASN1", volume=10.12, weight=20.34))
            asn_list.append(POAsnDetail(poOrderCode=po_order, asnOrderNum="ASN2", volume=10.12, weight=20.34))

        return POAsnResponse(poAsnList=asn_list)

    except HTTPException as e:
        # FastAPI 会自动返回带有错误代码和消息的响应
        raise e

    except Exception as e:
        # 捕获其他异常并返回500错误
        raise HTTPException(status_code=500, detail=str(e))